import streamlit as st
import pandas as pd
from db_utils import get_connection

def show_balance(card_id):
    """余额查询"""
    st.subheader("余额查询")
    conn = get_connection()
    if conn:
        try:
            cursor = conn.cursor(dictionary=True)
            cursor.execute("SELECT balance FROM cardInfo WHERE cardID = %s", (card_id,))
            result = cursor.fetchone()
            if result:
                st.success(f"账户余额: {result['balance']} 元")
            else:
                st.warning("未找到该银行卡信息")
        except Exception as e:
            st.error(f"查询失败: {e}")
        finally:
            cursor.close()
            conn.close()

def show_transactions(card_id):
    """交易查询"""
    st.subheader("交易查询")
    conn = get_connection()
    if conn:
        try:
            cursor = conn.cursor(dictionary=True)
            cursor.execute("CALL sp_get_card_transactions(%s)", (card_id,))
            result = cursor.fetchall()
            if result:
                df = pd.DataFrame(result)
                st.dataframe(df)
            else:
                st.info("无交易记录")
        except Exception as e:
            st.error(f"查询失败: {e}")
        finally:
            # Stored procedure cursors are a bit different
            # We need to ensure all results are fetched before closing
            while cursor.nextset():
                pass
            cursor.close()
            conn.close()


def deposit(card_id, amount, remark):
    """存款"""
    st.subheader("存款")
    conn = get_connection()
    if conn:
        try:
            cursor = conn.cursor()
            cursor.callproc('sp_transaction', (card_id, '存入', amount, remark))
            conn.commit()
            st.success(f"存款成功！金额：{amount:.2f}元。")
            st.rerun()
        except Exception as e:
            st.error(f"存款失败: {e}")
            conn.rollback()

def withdraw(card_id, amount, remark):
    """取款"""
    st.subheader("取款")
    conn = get_connection()
    if conn:
        try:
            cursor = conn.cursor()
            cursor.callproc('sp_transaction', (card_id, '支取', amount, remark))
            conn.commit()
            st.success(f"取款成功！金额：{amount:.2f}元。")
            st.rerun()
        except Exception as e:
            st.error(f"取款失败: {e}")
            conn.rollback()

def report_loss(card_id):
    """挂失"""
    st.subheader("挂失银行卡")
    conn = get_connection()
    if conn:
        try:
            cursor = conn.cursor()
            cursor.execute("UPDATE cardInfo SET IsReportLoss = TRUE WHERE cardID = %s", (card_id,))
            conn.commit()
            if cursor.rowcount > 0:
                st.success(f"卡号 {card_id} 挂失成功！")
                st.rerun()
            else:
                st.warning("未找到该银行卡或无需挂失")
        except Exception as e:
            st.error(f"挂失失败: {e}")
            conn.rollback()


def change_password(card_id, old_password, new_password):
    """修改密码"""
    if not (isinstance(new_password, str) and new_password.isdigit() and len(new_password) == 6):
        st.error("新密码必须是6位数字。")
        return

    conn = get_connection()
    if conn:
        try:
            cursor = conn.cursor()
            # 验证旧密码
            cursor.execute("SELECT pass FROM cardInfo WHERE cardID = %s", (card_id,))
            result = cursor.fetchone()
            if result and result[0] == old_password:
                # 更新密码
                cursor.execute("UPDATE cardInfo SET pass = %s WHERE cardID = %s", (new_password, card_id))
                conn.commit()
                st.success("密码修改成功！为保障安全，请使用新密码重新登录。")
                # Force logout for security
                if 'customer_logged_in' in st.session_state:
                    del st.session_state.customer_logged_in
                if 'card_id' in st.session_state:
                    del st.session_state.card_id
                st.rerun()
            else:
                st.error("旧密码错误或卡号不存在")
        except Exception as e:
            st.error(f"密码修改失败: {e}")
            conn.rollback()
        finally:
            cursor.close()
            conn.close()

def transfer_money(from_card_id, to_card_id, amount):
    """转账"""
    conn = get_connection()
    if conn:
        try:
            cursor = conn.cursor()
            args = [from_card_id, to_card_id, amount, '']
            result_args = cursor.callproc('sp_transfer_money', args)
            status = result_args[3]
            
            if status == '转账成功':
                st.success(f"转账成功！向卡号 {to_card_id} 转出 {amount:.2f}元。")
                conn.commit()
                st.rerun()
            else:
                st.error(status)
        except Exception as e:
            st.error(f"转账操作失败: {e}")
            conn.rollback()
        finally:
            cursor.close()
            conn.close() 